package com.shsh.safemanage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import com.shsh.safemanage.model.PostCheck;
import com.shsh.safemanage.model.User;

public class PostCheckDao {
	public List<PostCheck> getPostCheckList() {
		String sql = "select ID, CHECK_NAME from postcheck";
		List<PostCheck> postCheckList = new ArrayList<PostCheck>();
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				PostCheck postCheck = new PostCheck();
				postCheck.setId(rs.getLong("ID"));
				postCheck.setCheckName(rs.getString("CHECK_NAME"));
				
				postCheckList.add(postCheck);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return postCheckList;
	}
	
	public PostCheck getPostCheck(Long id) {
		String sql = "select ID, CHECK_NAME from postcheck where ID=?";
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);
			
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				PostCheck postcheck = new PostCheck();
				postcheck.setId(rs.getLong("ID"));
				postcheck.setCheckName(rs.getString("CHECK_NAME"));
				
				return postcheck;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return null;
	}
	
	public boolean addUserPostcheckMapping(User registerUser, Set<PostCheck> postcheckSet) {
		String selectSQL = "select ID from user_postcheck_mapping where USER_ID=? and POSTCHECK_ID=?";
		String sql = "insert into user_postcheck_mapping(USER_ID, POSTCHECK_ID) values(?, ?)";
		
		Connection conn = null;
		PreparedStatement pstm = null;
		PreparedStatement pstm1 = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(selectSQL);
			pstm1 = conn.prepareStatement(sql);
			for (PostCheck postcheck : postcheckSet) {
				pstm.setLong(1, registerUser.getId());
				pstm.setLong(2, postcheck.getId());
				
				ResultSet rs = pstm.executeQuery();
				if (!rs.next()) {
					pstm1.setLong(1, registerUser.getId());
					pstm1.setLong(2, postcheck.getId());
					
					pstm1.executeUpdate();
				}
			}
			
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return false;
	}
}
